VB.NET reading CSV files (odbc or oledb)

[Jesse Albert]

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

[cj]

It's seeing the ip address as a number. Somehow you need to designate
that it is a character field. Adding the "s around it does that but
there is probably another way. I'll be watching to see the solution myself.

Jesse Albert wrote:

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

[shriop]

You can use a schema.ini file to attempt to change this behavior,
http://support.microsoft.com/?kbid=210073 . If you get tired of the
crazyness with these drivers, you can optionally try using my parser
that I sell because of the awkwardness of these configurations,
http://www.csvreader.com .

Bruce Dunwiddie

cj wrote:

It's seeing the ip address as a number. Somehow you need to designate
that it is a character field. Adding the "s around it does that but
there is probably another way. I'll be watching to see the solution myself.

Jesse Albert wrote:

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

[james]

One way to see what is causing the problem is to try (as a test) importing
the CSV file into a Access Database table, using Access itself. If it does
the same thing there, you will at least get a good error message.
Otherwise,,,,,,,
In your connection string in your code change this: FMT=CSVDelimited"""
to: FTM=Delimited\"""
The rest of your code looks fine. Just did a test making the changes I
mentioned and it works fine.
james

"Jesse Albert" <Je*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

[james]

Forgot to mention one other thing, your date in the CSV file needs to be
enclosed in quotes:
"IPADDRESS","NAME","LOCATION"
"192.168.0.3","JOE","HERE"

otherwise, if you leave off the quotes, Access will strip off everything
past the second decimal point ( .0.3)
james

"Jesse Albert" <Je*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location

[james]

Let me try to type this a bit slower this time, your DATA in the CSV file
needs to be enclosed in quotes:
(then the rest of my example)
Sorry about that, been several hours since my last cup of coffee and I'm
having accuracy problems now!!! :-)
james

"james" <jj***************@earthlink.net> wrote in message
news:ej**************@TK2MSFTNGP05.phx.gbl...

Forgot to mention one other thing, your date in the CSV file needs to be
enclosed in quotes:
"IPADDRESS","NAME","LOCATION"
"192.168.0.3","JOE","HERE"

otherwise, if you leave off the quotes, Access will strip off everything
past the second decimal point ( .0.3)
james

"Jesse Albert" <Je*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Hiyo,
I'm trying to import a CSV file into a datatable using either ODBC or
OLEDB. One of the columns contains an IP Address. For some reason,
the IP address will not display correctly. All of the other
information in the CSV is accurate.

For example,
10.80.34.100 displays as 10.8034. All the IPs in the column are
displayed like this.

I'm not sure what is causing this error, I have tried using odbc and
the oledb drivers to load the csv and they both seem to cause that so
I'm not sure what I'm doing wrong. When I put quotes around the IP
addresses this fixes the issue, however I don't really want to use
quotes since I'm using commas for a seperate parsing script.

The code I use to load the data is (Right now it's using ODBC, the
commented codes are the oledb connection):

Dim sConnectionString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" & Directory & ";Extensions=asc,csv,tab,txt;"
Dim objConn As New Odbc.OdbcConnection(sConnectionString)
'Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory &
";Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
'Dim objConn As New
OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New Odbc.OdbcCommand("SELECT * FROM " &
FileName, objConn)
Dim objAdapter1 As New Odbc.OdbcDataAdapter

' Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM
" & FileName, objConn)
'Dim objAdapter1 As New OleDb.OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "Table1")
DataGridView1.DataSource =
objDataset1.Tables(0).DefaultView
objConn.Close()
An example line of the CSV is:
10.80.34.116,Name,Location

The datagridview would then display (columns seperated on newlines):
10.8034
Name
Location